DECLARE @XML XML = 
					(
					SELECT
						TOP 10 
						M.memid AS "@memid",
						LTRIM(RTRIM(ET.firstname)) AS "FirstName",
						LTRIM(RTRIM(ET.lastname)) AS "LastName",
						LTRIM(RTRIM(E.[description])) AS "Ethnicity"
					FROM
						plandata_parallel.dbo.member AS M
						INNER JOIN plandata_parallel.dbo.ethnicity AS E ON (M.ethnicid = E.ethnicid)
						INNER JOIN plandata_parallel.dbo.entity AS ET ON (M.entityid = ET.entid) 
					WHERE
						RTRIM(LTRIM(M.memid)) <> ''
					FOR XML PATH('member'), ROOT('members')
					)

SELECT @XML AS Test


--*****PATH*****
----XPATH NODENAME
--SELECT @XML.query('members') AS TEST

----XPATH /
--SELECT @XML.query('members/member') AS TEST

----XPATH //
--SELECT @XML.query('//member') AS TEST

----XPATH @
--SELECT @XML.query('//member[@memid]')
--SELECT @XML.query('//member[@NonExistantAttribute]')


--*****PREDICATE*****
----First member
--SELECT @XML.query('/members/member[1]') AS TEST

----Last member
--SELECT @XML.query('/members/member[last()]') AS TEST

----Second member using operators
--SELECT @XML.query('/members/member[1+1]') AS TEST

----Top 5
--SELECT @XML.query('/members/member[position()<=5]') AS TEST

----Attribute value query
--SELECT @XML.query('/members/member[@memid="IDAIM0000000060"]') AS TEST

----With an element value and the .. to get the parent node
--SELECT @XML.query('/members/member/Ethnicity[../FirstName="NANCY"]') AS TEST
--SELECT @XML.query('/members/member[Ethnicity="CAUCASIAN"][FirstName="DAWN"]') AS TEST

--FOR IRENE
--SELECT @XML.query('/members/member[Ethnicity="CAUCASIAN"][FirstName="DAWN"]') AS TEST


--*****UNKNOWNS*****
--SELECT @XML.query('/members/*') AS TEST
--SELECT @XML.query('/members/member[@*]') AS TEST




--*****EXIST*****

--SELECT @XML.exist('/members/member[Ethnicity="CAUCASIAN"]') AS Result
--SELECT @XML.exist('/members/member[Ethnicity="COWBOY"]') AS Result
--SELECT @XML.exist('//member[Ethnicity="CAUCASIAN"]') AS Result
--SELECT @XML.exist('//member[SomeUndefinedElement="FAIL"]') AS RESULT


--*****VALUE*****
--SELECT @XML.value('(//FirstName)[1]','varchar(max)')
--SELECT @XML.value('(//FirstName)[2]','varchar(max)')




--*****NODES*****

----XML SHREDDING
--SELECT
--	FirstName = FieldAlias.value('(FirstName)[1]','varchar(max)'),
--	Ethnicity = FieldAlias.value('(Ethnicity)[1]','varchar(max)')

--FROM
--	@XML.nodes('/members/member') AS TableAlias(FieldAlias)

--XML SHREDDING WITH CTE
--;WITH ShreddedDataTable AS
--(
--	SELECT
--		FirstName = FieldAlias.value('(FirstName)[1]','varchar(max)'),
--		Ethnicity = FieldAlias.value('(Ethnicity)[1]','varchar(max)')
--	FROM
--		@XML.nodes('/members/member') AS TableAlias(FieldAlias)
--)
--SELECT
--	FirstName, Ethnicity
--FROM
--	ShreddedDataTable
--WHERE
--	Ethnicity = 'CAUCASIAN'




--*****QUERY ADDITIONAL STUFF*****

--Querying all attributes
--SELECT @XML.query('
--<members>
--{
--	for $m in /members/member
--	where $m/@Ethnicity="CAUCASIAN"
--	order by $m/@memid ascending
--	return <member memid="{data($m/@memid)}" firstName="{data($m/@FirstName)}"/>
--}
--</members>
--');

----Querying all elements
--SELECT @XML.query('
--<members>
--{
--	for $m in /members/member
--	where ($m/Ethnicity[text()="CAUCASIAN"])
--	order by $m/@memid ascending
--	return <ul <li> memid="{data($m/@memid)}"/>
--}
--</members>
--');







